/*
 * SimpleTableDemo.java requires no other files.
 */

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;

import java.sql.*;
import java.text.SimpleDateFormat;

public class Table extends JPanel {
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private boolean DEBUG = false;

    public Table() {
        super(new GridLayout(1,0));

        
        String[] columnNames = {"id",
                                "cur1",
                                "cur2",
                                "buy",
                                "sell",
                                "time"};
        
        Object[][] data;
        data = this.getData();
        
        final JTable table = new JTable(data, columnNames);
        table.setEnabled(false);
        table.setAutoCreateRowSorter(true);
        table.setPreferredScrollableViewportSize(new Dimension(500, 70));
        table.setFillsViewportHeight(true);

        if (DEBUG) {
            table.addMouseListener(new MouseAdapter() {
                public void mouseClicked(MouseEvent e) {
                    printDebugData(table);
                }
            });
        }

        //Create the scroll pane and add the table to it.
        JScrollPane scrollPane = new JScrollPane(table);

        //Add the scroll pane to this panel.
        add(scrollPane);
    }

    private Object[][] getData() {
    	Connection conn = null;
    	Object[][] data = null;
		try {
			// Load the JDBC driver
			Class.forName ("com.mysql.jdbc.Driver").newInstance();
			
			// Create a connection to the database
			String serverName = "mysql300.ixwebhosting.com";
			String mydatabase = "bnahill_ace";
			String url = "jdbc:mysql://" + serverName +  "/" + mydatabase;
			String username = "bnahill_ace";
			String password = "aceace";
			
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			System.out.println("Could not find database driver!");
		} catch (SQLException e) {
			System.out.println("Could not connect to the database!");
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		
		try {
			Statement s = conn.createStatement();
			s.executeQuery("SELECT * FROM prices ORDER BY time DESC LIMIT 50");
			ResultSet rs = s.getResultSet();
			ResultSetMetaData metaData = rs.getMetaData();
			rs.last();
			int rows = rs.getRow();
			// move back to first
			rs.first();
			
			System.out.println("Max rows: "+rows+", column count: "+metaData.getColumnCount());
			
			data = new Object[rows][metaData.getColumnCount()];
			SimpleDateFormat d = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
			
			int counter = rows-1;
			do {								
				data[counter][0] = new Integer(rs.getRow());
				data[counter][1] = new String(rs.getString("cur1"));
				data[counter][2] = new String(rs.getString("cur2"));
				data[counter][3] = new Float(rs.getFloat("buy"));
				data[counter][4] = new Float(rs.getFloat("sell"));
				data[counter][5] = new String(d.format(new Date(rs.getLong("time")*1000)));
				counter--;
			} while (rs.next());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			conn.close ();
			System.out.println ("Database connection terminated");
        } catch (Exception e) { /* ignore close errors */ }
        
		return data;
    }
    
    private void printDebugData(JTable table) {
        int numRows = table.getRowCount();
        int numCols = table.getColumnCount();
        javax.swing.table.TableModel model = table.getModel();

        System.out.println("Value of data: ");
        for (int i=0; i < numRows; i++) {
            System.out.print("    row " + i + ":");
            for (int j=0; j < numCols; j++) {
                System.out.print("  " + model.getValueAt(i, j));
            }
            System.out.println();
        }
        System.out.println("--------------------------");
    }

    /**
     * Create the GUI and show it.  For thread safety,
     * this method should be invoked from the
     * event-dispatching thread.
     */
    private static void createAndShowGUI() {
        //Create and set up the window.
        JFrame frame = new JFrame("SimpleTableDemo");
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        //Create and set up the content pane.
        Table newContentPane = new Table();
        newContentPane.setOpaque(true); //content panes must be opaque
        frame.setContentPane(newContentPane);

        //Display the window.
        frame.pack();
        frame.setVisible(true);
    }

    public static void main(String[] args) {
        //Schedule a job for the event-dispatching thread:
        //creating and showing this application's GUI.
        javax.swing.SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                createAndShowGUI();
            }
        });
    }
}
